﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMS.DAL.DAHelpers;
using SMS.Entities;
using System.Data;

namespace SMS.DAL
{
    public class SupplierDA : BaseDA<Supplier>
    {


        public override Supplier SaveObject(string userName, Supplier tObject)
        {
            #region Procedure
            /*

CREATE PROCEDURE [dbo].[USP_INS_UPDATE_SUPPLIER]  
(@Supplier_ID int,  
@Supplier_Name varchar(50),  
@Supplier_Address varchar(500),  
@CityID int,  
@Supplier_Mobile varchar(20), 

@Supplier_AlternateMobile varchar(20), 
@Supplier_Email varchar(20),  
@IsLive bit)  
as  
IF (@Supplier_ID=0)  
BEGIN  
INSERT INTO [shop].[dbo].[Supplier]
           ([Supplier_Name]
           ,[Supplier_Address]
           ,[CityID]
           ,[Supplier_Mobile]
           ,[Supplier_AlternateMobile]
           ,[Supplier_Email]
           ,[IsLive])
     VALUES
           (@Supplier_Name
           ,@Supplier_Address
           ,@CityID
           ,@Supplier_Mobile
           ,@Supplier_AlternateMobile
           ,@Supplier_Email
           ,@IsLive)


 
     SELECT @@IDENTITY  
END  
ELSE  
BEGIN             
UPDATE [shop].[dbo].[Supplier]
   SET [Supplier_Name] = @Supplier_Name
      ,[Supplier_Address] = @Supplier_Address
      ,[CityID] =@CityID
      ,[Supplier_Mobile] =@Supplier_Mobile
      ,[Supplier_AlternateMobile] =@Supplier_AlternateMobile
      ,[Supplier_Email] = @Supplier_Email
      ,[IsLive] =@IsLive
 WHERE Supplier_ID=@Supplier_ID 
 END
*/

            #endregion

            try
            {
                dbHelper.AddParameter("@Supplier_ID", tObject.Supplier_ID);
                dbHelper.AddParameter("@Supplier_Name", tObject.Supplier_Name);
                dbHelper.AddParameter("@Supplier_Address", tObject.Supplier_Address);
                dbHelper.AddParameter("@CityID", tObject.Supplier_City.CityID);
                dbHelper.AddParameter("@Supplier_Mobile", tObject.Supplier_Mobile);
                dbHelper.AddParameter("@Supplier_AlternateMobile", tObject.Supplier_AlternateMobile);
                dbHelper.AddParameter("@Supplier_Email", tObject.Supplier_Email);
                dbHelper.AddParameter("@IsLive", tObject.IsLive);
                object objID = dbHelper.ExecuteScaler("USP_INS_UPDATE_SUPPLIER", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Closed);
                if (Convert.ToInt32(objID) > 0)
                    tObject.Supplier_ID = Convert.ToInt32(objID);
                LogActivity(userName, string.Format("Supplier Saved with ID {0}", tObject.Supplier_ID), tObject);
                return tObject;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, tObject);
                throw new Exception("Error Occured while Saving Supplier");
            }
        }

        public override bool DeleteObject(string userName, Supplier tObject)
        {
            throw new NotImplementedException();
        }

        public override List<Supplier> GetObjectList(string userName, DateTime fromDate, DateTime toDate)
        {
            throw new NotImplementedException();
        }

        public override List<Supplier> SearchObject(string userName, string SearchText)
        {
            #region "Procedure"
            /*
               
CREATE PROCEDURE [dbo].[USP_SEARCH_SUPPLIER] 
(@SearchText VARCHAR(256))    
AS    
SELECT [Supplier_ID]
      ,[Supplier_Name]
      ,[Supplier_Address]
      ,[CityID]
      ,[Supplier_Mobile]
      ,[Supplier_AlternateMobile]
      ,[Supplier_Email]
      ,[IsLive]
  FROM [shop].[dbo].[Supplier]   
  where IsLive=1 and ([Supplier_ID] LIKE '%'+ @SearchText+'%'    
  or [Supplier_Name] LIKE '%'+ @SearchText+'%')    
  order by [Supplier_Name],[Supplier_ID] 
             */
            #endregion
            try
            {
                if (string.IsNullOrWhiteSpace(SearchText))
                    SearchText = string.Empty;
                dbHelper.AddParameter("@SearchText", SearchText);
                IDataReader dr = dbHelper.ExecuteReader("USP_SEARCH_SUPPLIER", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<Supplier>();
                while (dr.Read())
                {
                    list.Add(GetSupplierObject(dr, false));
                }
                return list;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Error while searching Supplier");
            }
            #region
            /*
             * 
  
CREATE PROCEDURE [dbo].[USP_SEARCH_SUPPLIER] 
(@SearchText VARCHAR(256))    
AS    
SELECT [Supplier_ID]
      ,[Supplier_Name]
      ,[Supplier_Address]
      ,[CityID]
      ,[Supplier_Mobile]
      ,[Supplier_AlternateMobile]
      ,[Supplier_Email]
      ,[IsLive]
  FROM [shop].[dbo].[Supplier]   
  where IsLive=1 and ([Supplier_ID] LIKE '%'+ @SearchText+'%'    
  or [Supplier_Name] LIKE '%'+ @SearchText+'%')    
  order by [Supplier_Name],[Supplier_ID] 
             * */
            #endregion
        }

        public Supplier GetCompleteSupplierObject(string userName, int Id)
        {
            try
            {

                dbHelper.AddParameter("@Supplier_ID", Id);
                IDataReader dr = dbHelper.ExecuteReader("USP_SELECT_SUPPLIER", CommandType.StoredProcedure, ConnectionState.Closed);
                LogActivity(userName, "Complete Supplier object loaded with Id " + Id, null);
                if (dr.Read())
                {
                    var obj = GetSupplierObject(dr, true);
                    return obj;
                }
                return null;

            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Error while Getting Supplier with id "+ Id);
            }
        }

        private Supplier GetSupplierObject(IDataReader dr, bool IsFull)
        {
            Supplier obj = new Supplier();
            int i = -1;
            if (!dr.IsDBNull(++i))
                obj.Supplier_ID = dr.GetInt32(i);
            if (!dr.IsDBNull(++i))
                obj.Supplier_Name = dr.GetString(i);
            if (IsFull)
            {
                if (!dr.IsDBNull(++i))
                    obj.Supplier_Address = dr.GetString(i);
                if (!dr.IsDBNull(++i))
                    obj.Supplier_City = new City { CityID = dr.GetInt32(i), CityName = dr.IsDBNull(++i) ? string.Empty : dr.GetString(i) };
                if (!dr.IsDBNull(++i))
                    obj.Supplier_Mobile = dr.GetString(i);
                if (!dr.IsDBNull(++i))
                    obj.Supplier_AlternateMobile = dr.GetString(i);
                if (!dr.IsDBNull(++i))
                    obj.Supplier_Email = dr.GetString(i);
                if (!dr.IsDBNull(++i))
                    obj.IsLive = dr.GetBoolean(i++);
            }
            return obj;
        }
    }
}
